package hr.dao;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;

import javax.faces.model.SelectItem;

import resources.bean.Login;
import resources.dao.DatabaseDAO;
import resources.pojo.hr_attendance;
import resources.pojo.hr_attendance_type;
import resources.pojo.hr_department;
import resources.pojo.hr_designation;
import resources.pojo.hr_employee;
import resources.pojo.hr_list_attendance;
import resources.util.Tool;

public class AttendanceDAO extends DatabaseDAO{
	public static String action(String action,hr_attendance attendance){
		String sql = "call sp_hr_attendance(?,?,?,?,?,?,?,?,?,?,?)";
		String result = "";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,action);
			cs.registerOutParameter(2,Types.VARCHAR);
			cs.setString(3,attendance.getId());
			cs.setString(4, attendance.getEmployee().getId());
			cs.setString(5,Tool.format2.format(attendance.getDate()));
			cs.setString(6, Tool.format7.format(attendance.getTime_in()));
			cs.setString(7, Tool.format7.format(attendance.getTime_out()));
			cs.setString(8, attendance.getType().getId());
			cs.setString(9, attendance.getDescription());
			cs.setInt(10, attendance.getStatus());
			cs.setString(11, Login.user.getUsername());
			cs.executeUpdate();
			result = cs.getString(2);			
			conn.commit();			
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return result;
		} finally {
			closeConnection();
		}
		return result;
	}
	
	public static ArrayList<hr_list_attendance> getListAttendancePaging(Date date,String department,String name,int page, int row){
		ArrayList<hr_list_attendance> temp;
		ArrayList<hr_attendance> listAttendance;
		String sdate = Tool.format2.format(date);
		String sql = "call sp_hr_attendance_paging(?,?,?,?,?,?,?)";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(2,sdate);
			cs.setString(3,"%"+department+"%");
			cs.setString(4,"%"+name+"%");
			cs.setInt(5,page);
			cs.setInt(6,row);
			cs.registerOutParameter(7, Types.INTEGER);
			
			cs.setString(1, "list-employee");			
			rs = cs.executeQuery();
			temp = new ArrayList<hr_list_attendance>();
			hr_employee emp;
			hr_list_attendance laten;
			while(rs.next()){
				laten= new hr_list_attendance();
				emp = new hr_employee();
				emp.setId(rs.getString("e.id"));
				emp.setFirstname(rs.getString("e.firstname"));
				emp.setLastname(rs.getString("e.lastname"));
				emp.setGender(rs.getInt("e.gender"));
				emp.setEmail(rs.getString("e.email"));	
				emp.setNumber(rs.getString("e.number"));
				emp.setCellphone(rs.getString("e.cellphone"));
				emp.setDepartment(new hr_department());
				emp.getDepartment().setName(rs.getString("d.name"));
				emp.setDesignation(new hr_designation());
				emp.getDesignation().setName(rs.getString("de.name"));
				laten.setEmployee(emp);
				temp.add(laten);
			}
			
			cs.setString(1, "list-attendance");
			rs = cs.executeQuery();
			listAttendance = new ArrayList<hr_attendance>();
			hr_attendance aten = null;
			while(rs.next()){
				aten = new hr_attendance();
				aten.setId(rs.getString("a.id"));
				aten.setEmployee(new hr_employee());
				aten.getEmployee().setId(rs.getString("a.employee"));
				aten.setDate(rs.getDate("a.date"));
				aten.setTime_in(rs.getTime("a.time_in"));
				aten.setTime_out(rs.getTime("a.time_out"));
				aten.setType(new hr_attendance_type());
				aten.getType().setId(rs.getString("a.type"));
				aten.setDescription(rs.getString("a.description"));
				aten.setStatus(rs.getInt("a.status"));
				aten.setCreated_by(rs.getString("a.created_by"));
				aten.setCreated_date(rs.getTimestamp("a.created_date"));
				aten.setModified_by(rs.getString("a.modified_by"));
				aten.setModified_date(rs.getTimestamp("a.modified_date"));
				listAttendance.add(aten);
			}
			
			for(int i=0;i<temp.size();i++){
				for(int y = 0;y<listAttendance.size();y++){
					if(temp.get(i).getEmployee().getId().equals(listAttendance.get(y).getEmployee().getId())){
						temp.get(i).setAttendance(listAttendance.get(y));
						break;
					}
				}
			}
			if(temp.size() > 0){
				Tool.page = cs.getInt(7);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {
			closeConnection();
		}
		return temp;
	}
	
	public static ArrayList<SelectItem> getAttendanceSelect(){
		ArrayList<SelectItem> temp = new ArrayList<SelectItem>();
		String sql = "call sp_hr_attendance_select(?,?)";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,"select_item");
			cs.setString(2,"");
			rs = cs.executeQuery();
			while(rs.next()){
				temp.add(new SelectItem(rs.getString("id"),rs.getString("name")));
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {
			closeConnection();
		}
		return temp;
	}
	
}
